﻿Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections.ObjectModel

Namespace SQLDataAccess

    ''' <summary>
    ''' This class provides simple methods to load business entity object(s) and to run the ExecuteNonQuery method.
    ''' </summary>
    ''' <typeparam name="T">Type of entity object to load.</typeparam>
    Public Class EntityLoader(Of T)

#Region " Constructors "

        Private _enumReturnCode As DatabaseReturnCode = DatabaseReturnCode.NotSet
        Private _intAffectedRows As Integer = -1

#End Region

#Region " Properties "

        ''' <summary>
        ''' Gets the number of rows affected by the data operation.
        ''' </summary>
        ''' <returns>Integer</returns>
        Public ReadOnly Property AffectedRows() As Integer
            Get
                Return _intAffectedRows
            End Get
        End Property

        ''' <summary>
        ''' Gets the ReturnCode from the data operation
        ''' </summary>
        ''' <returns>Integer</returns>
        Public ReadOnly Property ReturnCode() As DatabaseReturnCode
            Get
                Return _enumReturnCode
            End Get
        End Property

#End Region

#Region " Constructors "

        Public Sub New()
        End Sub

#End Region

#Region " Methods "

        Public Function ExecuteNonQuery(ByVal strConnectionStringKey As String, ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandTimeOut As Integer, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As Integer

            Dim da As New DataAccess(DataAccessConnection.GetConnectionString(strConnectionStringKey))
            _intAffectedRows = da.ExecuteNonQuery(CommandType, CommandText, CommandTimeOut, Parameters)
            _enumReturnCode = da.ReturnCode
            Return _intAffectedRows
        End Function

        Public Function LoadList(ByVal strConnectionStringKey As String, ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandBehavior As System.Data.CommandBehavior, ByVal CommandTimeOut As Integer, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As List(Of T)

            Dim objResult As New List(Of T)
            Dim da As New DataAccess(DataAccessConnection.GetConnectionString(strConnectionStringKey))
            Using reader As SqlClient.SqlDataReader = da.GetDataReader(CommandType, CommandText, CommandBehavior, Parameters)

                Dim objBuilder As New ReflectionBuilder(Of T)

                While reader.Read
                    objResult.Add(objBuilder.Build(reader))
                End While

            End Using
            _intAffectedRows = objResult.Count
            _enumReturnCode = DatabaseReturnCode.Successful
            Return objResult
        End Function

        Public Function LoadObservableColleciton(ByVal strConnectionStringKey As String, ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandBehavior As System.Data.CommandBehavior, ByVal CommandTimeOut As Integer, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As ObservableCollection(Of T)

            Dim objResult As New ObservableCollection(Of T)
            Dim da As New DataAccess(DataAccessConnection.GetConnectionString(strConnectionStringKey))
            Using reader As SqlClient.SqlDataReader = da.GetDataReader(CommandType, CommandText, CommandBehavior, Parameters)

                Dim objBuilder As New ReflectionBuilder(Of T)

                While reader.Read
                    objResult.Add(objBuilder.Build(reader))
                End While

            End Using
            _intAffectedRows = objResult.Count
            _enumReturnCode = DatabaseReturnCode.Successful
            Return objResult
        End Function

        Public Function LoadOne(ByVal strConnectionStringKey As String, ByVal CommandType As System.Data.CommandType, ByVal CommandText As String, ByVal CommandBehavior As System.Data.CommandBehavior, ByVal CommandTimeOut As Integer, ByVal ParamArray Parameters() As SqlClient.SqlParameter) As T

            Dim da As New DataAccess(DataAccessConnection.GetConnectionString(strConnectionStringKey))
            _intAffectedRows = 0
            Using reader As SqlClient.SqlDataReader = da.GetDataReader(CommandType, CommandText, CommandBehavior, Parameters)

                Dim objBuilder As New ReflectionBuilder(Of T)

                While reader.Read
                    _intAffectedRows += 1
                    _enumReturnCode = DatabaseReturnCode.Successful
                    Return objBuilder.Build(reader)
                End While

            End Using
            _enumReturnCode = DatabaseReturnCode.RecordNotFound
            Return Nothing
        End Function

#End Region

    End Class

End Namespace
